package dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.Enumeration;
import java.util.Hashtable;

import vo.Status;
import JDBC.DatabaseDao;
import Utils.DateUtils;
import Utils.MathUtils;
import Utils.POSTool;

public class StatusDao {

	static Connection con = DatabaseDao.getConnection();

	/**
	 * 
	 * @author 韩 闻文
	 * @return user_id数组
	 */
	public static String[] getAllIds() {
		String[] result = new String[10000000];
		try {
			String sql = "select * from statuses";
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			int i = 0;
			while (rs.next()) {
				result[i] = rs.getString("user_id");
				i++;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * @author 韩 闻文 get statuses by different kinds of statuses
	 * @return
	 */
	public static ArrayList<Status> getByCondition(Status status) {
		ArrayList<Status> result = new ArrayList<Status>();
		try {
			String sql = initCondition(status);
			// System.out.println(sql);
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Status tmp = new Status();
				tmp.setStatusId(rs.getString("status_id"));
				tmp.setUserId(rs.getString("user_id"));
				tmp.setContent(rs.getString("content"));
				tmp.setSourceName(rs.getString("source_name"));
				tmp.setCreatedAt(rs.getTimestamp("created_at"));
				result.add(tmp);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * @author 韩 闻文 get count of statuses by different kinds of statuses
	 * @return
	 */
	public static int getCountByCondition(Status status) {
		int result = 0;
		try {
			String sql = initCountCondition(status);
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			rs.next();
			result = rs.getInt(1);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	public static String initCondition(Status status) {
		String result = "select * from statuses";
		if (status == null)
			return result;

		boolean firstCondition = true;
		result += " where ";
		if (status.getUserId() != null && !status.getUserId().equals("")) {
			result += "user_id='" + status.getUserId() + "'";
			firstCondition = false;
		}
		if (status.getStartTime() != null && !status.getStartTime().equals("")) {
			if (firstCondition == false)
				result += " and ";
			result += "created_at> '" + status.getStartTime() + "'";
			firstCondition = false;
		}
		if (status.getEndTime() != null && !status.getEndTime().equals("")) {
			if (firstCondition == false)
				result += " and ";
			result += "created_at <'" + status.getEndTime() + "'";
			firstCondition = false;
		}
		if (status.getSourceName() != null && !status.getSourceName().equals("")) {
			if (firstCondition == false)
				result += " and ";
			result += "source_name='" + status.getSourceName() + "'";
			firstCondition = false;
		}
		return result;
		//返回的result为一条带条件限定的查询语句

	}

	public static String initCountCondition(Status status) {
		String result = "select count(*) from statuses";
		if (status == null)
			return result;

		boolean firstCondition = true;
		result += " where ";
		if (status.getUserId() != null && !status.getUserId().equals("")) {
			result += "user_id='" + status.getUserId() + "'";
			firstCondition = false;
		}
		if (status.getStartTime() != null && !status.getStartTime().equals("")) {
			if (firstCondition == false)
				result += " and ";
			result += "created_at> '" + status.getStartTime() + "'";
			firstCondition = false;
		}
		if (status.getEndTime() != null && !status.getEndTime().equals("")) {
			if (firstCondition == false)
				result += " and ";
			result += "created_at <'" + status.getEndTime() + "'";
			firstCondition = false;
		}
		if (status.getSourceName() != null && !status.getSourceName().equals("")) {
			if (firstCondition == false)
				result += " and ";
			result += "source_name='" + status.getSourceName() + "'";
			firstCondition = false;
		}
		return result;
		//result查询语句返回满足限定条件的条目数

	}

	/**
	 * @author: huangshuigui int getWordCount(String statusId)
	 *          和CommentDao中的getWordCount接口类似，只是sql语句不一样 应该有更好的设计模式来减少这种重复 TODO
	 *          untested
	 */
	public static int getWordCount(String content) {
		int result = 0;
		try {
			if (content != null) {
				String pieces = POSTool.SegmentSentence(content);
				if (pieces == null)
					return 0;
				if (pieces.length() > 0) {
					String[] terms = pieces.split(" +");
					result = terms.length;
				} else {
					result = 0;
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * @author 韩 闻文 user ranking by comment count
	 * @param topNumber
	 * @return
	 */
	public static String getStatusRankingByComments(int topNumber) {
		String result = "comment count,status content\n";
		try {
			String sql1 = "select top " + topNumber + " status_id,COUNT(*) from comments group by status_id order by COUNT(*) desc";
			Statement stmt1 = con.createStatement();
			ResultSet rs1 = stmt1.executeQuery(sql1);
			while (rs1.next()) {
				String status_id = rs1.getString("status_id");
				String count = rs1.getString(2);

				String sql2 = "select * from statuses where status_id=" + status_id;
				Statement stmt2 = con.createStatement();
				ResultSet rs2 = stmt2.executeQuery(sql2);
				rs2.next();
				String statusText = rs2.getString("content");

				result += count + "," + statusText + "\n";
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * @author 韩 闻文 created time of all statuses of a certain user
	 * @param userId
	 * @return
	 */
	public static ArrayList<Date> getStatusTimesByUserId(String userId) {
		ArrayList<Date> dateTimes = new ArrayList<Date>();
		try {
			String sql = "select * from statuses where user_id=" + userId + "";
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Date dateTime = rs.getTimestamp("created_at");
				dateTimes.add(dateTime);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return dateTimes;
	}

	/**
	 * 读取所有的statusesCount
	 * 
	 * @author 韩 闻文
	 * @return
	 */
	public static ArrayList<Integer> getStatusesCounts() {
		ArrayList<Integer> statusesCounts = new ArrayList<Integer>();
		try {
			String sql = "select * from users";
			System.out.println(sql);
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Integer statusesCount = rs.getInt("statuses_count");
				statusesCounts.add(statusesCount);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return statusesCounts;
	}

	/**
	 * 读取所有的statusesCount
	 * 
	 * @author 韩 闻文
	 * @return
	 */
	public static Hashtable<String, Integer> getUserIdStatusesCount() {
		Hashtable<String, Integer> userId_statusesCount = new Hashtable<String, Integer>();
		try {
			String sql = "select count(*),user_id from statuses group by user_id";
			System.out.println(sql);
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				int count = rs.getInt(1);
				String userId = rs.getString(2);
				userId_statusesCount.put(userId, count);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return userId_statusesCount;
	}

	/**
	 * 读取所有的statusesCount
	 * 
	 * @author 韩 闻文
	 * @return
	 */
	public static ArrayList<Integer> getStatusesLengths() {
		ArrayList<Integer> statusesLengths = new ArrayList<Integer>();
		try {
			String sql = "select * from statuses";
			System.out.println(sql);
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				String status = rs.getString("content");
				statusesLengths.add(status.length());
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return statusesLengths;
	}

	/**
	 * @author 韩 闻文 average status interval of a certain user
	 * @param userId
	 * @return
	 */
	public static String getAverageStatusFrequencyByUserId(String userId) {
		ArrayList<Date> dateTimes = getStatusTimesByUserId(userId);
		ArrayList<Integer> intervals = DateUtils.getIntervals(dateTimes);
		double averageInterval = MathUtils.getAverage(intervals);
		System.out.println(averageInterval);
		return String.valueOf(averageInterval);
	}

	/**
	 * @author 韩 闻文 total comments
	 * 
	 * @return
	 */
	public static int getLength(String statusId) {
		int result = 0;
		try {
			String sql = "select content from statuses where status_id=" + statusId;
			System.out.println(sql);
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			rs.next();
			result = rs.getString(1).length();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * @author 韩 闻文 average length of all statuses, unit is "characters" not
	 *         "words"
	 * @return
	 */
	public static double getAVGLength() {
		double result = 0;
		double sum = 0.0;
		int count = 0;
		try {
			String sql = "select content from statuses";
			System.out.println(sql);
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				sum += rs.getString(1).length();
				System.out.println(rs.getString(1).length());
				count++;
			}
			result = sum / count;
			System.out.println("sum,count:" + sum + "," + count);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * @author 韩 闻文 average length of all statuses, unit is "words" not
	 *         "characters"
	 * @return
	 */
	public static double getAVGWordCount() {
		double result = 0;
		double sum = 0.0;
		int count = 0;
		try {
			String sql = "select content from statuses";
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				sum += getWordCount(rs.getString("content"));
				count++;
			}
			result = sum / count;
			System.out.println("sum,count:" + sum + "," + count);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * @author 韩 闻文 average length of all statuses, unit is "words" not
	 *         "characters"
	 * @return
	 */
	public static double getALLWordCount() {
		double result = 0;
		try {
			String sql = "select content from statuses";
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				result += getWordCount(rs.getString("content"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * @author 韩 闻文 length of all statuses, unit is "characters" not "words"
	 * @return
	 */
	public static double getALLLength() {
		double result = 0;
		try {
			String sql = "select content from statuses";
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				result += rs.getString("content").length();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * @author 韩 闻文 length of all statuses, unit is "characters" not "words"
	 * @return
	 */
	public static ArrayList<Integer> getALLLengths() {
		ArrayList<Integer> result = new ArrayList<Integer>();
		try {
			String sql = "select length from statuses";
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				result.add(rs.getInt("length"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * @author 韩 闻文 get all source types
	 * @return
	 */
	public static ArrayList<String> getSourceTypes() {
		ArrayList<String> result = new ArrayList<String>();
		try {
			String sql = "select source_name from statuses group by source_name order by count(*) desc";
			System.out.println(sql);
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				result.add(rs.getString(1));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * @author 韩 闻文 get retweeted times of a status
	 * @return
	 */
	public static int getRetweetedCountById(String statusId) {
		int result = 0;
		try {
			String sql = "select count(*) from statuses where retweeted_status_id=" + statusId;
			System.out.println(sql);
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			rs.next();
			result = rs.getInt(1);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * @author 韩 闻文 insert data to word frequency table
	 * @return
	 */
	public static void insertWordFrequencyMapping() {
		Hashtable<String, Double> word_frequency = new Hashtable<String, Double>();
		try {
			String sql = "select content from statuses";
			System.out.println(sql);
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				String words[] = POSTool.SegmentSentence(rs.getString(1)).split(" +");// filter
																						// spaces
				for (int i = 0; i < words.length; i++) {
					String word = words[i];
					if (word_frequency.containsKey(word))
						word_frequency.put(word, word_frequency.get(word) + 1);
					else
						word_frequency.put(word, 1.0);
				}
			}
			Enumeration keys = word_frequency.keys();
			while (keys.hasMoreElements()) {
				String word = (String) keys.nextElement();
				Double frequency = word_frequency.get(word);
				if (word.equals("") || word.equals("'"))
					continue;
				sql = "insert into word_frequency values('" + word + "'," + frequency + ");";
				DatabaseDao.updateData(sql);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * @author 韩 闻文 insert data to word frequency table
	 * @return
	 */
	public static void insertWordFrequencyUserMapping() {
		try {
			// read all userIds
			ArrayList<String> userIds = new ArrayList<String>();
			String sql = "select user_id from users";
			System.out.println(sql);
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				userIds.add(rs.getString(1));
			}

			for (int i = 0; i < userIds.size(); i++) {
				Hashtable<String, Double> word_frequency = new Hashtable<String, Double>();
				sql = "select content from statuses where user_id=" + userIds.get(i);
				System.out.println(sql);
				stmt = con.createStatement();
				rs = stmt.executeQuery(sql);
				while (rs.next()) {
					String words[] = POSTool.SegmentSentence(rs.getString(1)).split(" +");// filter
																							// spaces
					for (int j = 0; j < words.length; j++) {
						String word = words[j];
						if (word_frequency.containsKey(word))
							word_frequency.put(word, word_frequency.get(word) + 1);
						else
							word_frequency.put(word, 1.0);
					}
				}
				Enumeration keys = word_frequency.keys();
				while (keys.hasMoreElements()) {
					String word = (String) keys.nextElement();
					Double frequency = word_frequency.get(word);
					if (word.equals("") || word.equals("'"))
						continue;
					sql = "insert into word_frequency_user values('" + word + "'," + frequency + "," + userIds.get(i) + ");";
					DatabaseDao.updateData(sql);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * @author 韩 闻文 return frequency of a word
	 * @return
	 */
	public static double getWordFrequency(String word) {
		Double result = 0.0;
		try {
			String sql = "select frequency from word_frequency where word='" + word + "'";
			System.out.println(sql);
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			rs.next();
			result = rs.getDouble(1);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * @author 韩 闻文 return frequency of a word used by a user
	 * @return
	 */
	public static double getWordFrequencyByUserId(String word, String userId) {
		Double result = 0.0;
		try {
			String sql = "select frequency from word_frequency_user where word='" + word + "' and user_id=" + userId;
			System.out.println(sql);
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			rs.next();
			result = rs.getDouble(1);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	// select * from statuses where update_time>'2012-04-02 00:00:00' and
	// update_time<'2012-5-02 00:00:00';
	/**
	 * @author 韩 闻文 the crawl time of the first and last statuses
	 * @return
	 */
	public static String getCrawledTimeRange() {
		String result = "";
		try {
			String sql = "select min(update_time),max(update_time)  from statuses ";
			System.out.println(sql);
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			rs.next();
			result += rs.getString(1) + "@";
			result += rs.getString(2);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * @author 韩 闻文 the crawl time of the first and last statuses
	 * @return
	 */
	public static String getCreatedTimeRange() {
		String result = "";
		try {
			String sql = "select min(created_at),max(created_at)  from statuses ";
			System.out.println(sql);
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			rs.next();
			result += rs.getString(1) + "@";
			result += rs.getString(2);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * 计算一个用户所有的status的长度和
	 * 
	 * @author 韩 闻文
	 * @param userId
	 * @return
	 */
	public static int getLengthsByUserId(String userId) {
		int result = 0;
		try {
			String sql = "select len(content) from statuses where user_id=" + userId;
			Statement stmt = DatabaseDao.getConnection().createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				result += rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		return result;

	}

	public static void main(String[] args) {
		// int length = getLength("2951529769");
		// System.out.println(length);
		// ArrayList<String> sourceTypes=getSourceTypes();
		// System.out.println(sourceTypes);
		// int retweetedCount=getRetweetedCountById("2983506973");
		// System.out.println(retweetedCount);
		// int count=StatusDao.getWordCount("3425745382785595");
		// System.out.println(count);
		// double avgLength=getAVGLength();
		// System.out.println(avgLength);
		// insertWordFrequencyMapping();
		// insertWordFrequencyUserMapping();
		// double count=getWordFrequency("我们");
		// System.out.println("count:"+count);
		// double countByUser=getWordFrequencyByUserId("我们","1642634100");
		// System.out.println("countByUser:"+countByUser);
		// String crawledTimeRange=StatusDao.getCrawledTimeRange();
		// System.out.println(crawledTimeRange);
		// System.out.println(statuses.toString());
		// System.out.println(statusCount);
		// System.out.println(statusLength);
		double count = getAVGWordCount();
		System.out.println(count);

	}
}
